IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAdminContactInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAdminContactInfo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.[GetAdminContactInfo]        
@instId INT,
@chiefCode VARCHAR(50),
@lastName VARCHAR(50),
@firstName VARCHAR(50)

AS        
BEGIN          
 SET NOCOUNT ON  

DECLARE @institutionId Varchar(50)
DECLARE @strChiefCode Varchar(50)

IF @instId = 0 
  SET @institutionId = ''
ELSE
  SET @institutionId = CONVERT(VARCHAR,@instId)

IF @chiefCode ='0'
 SET @strChiefCode =''
ELSE
 SET @strChiefCode = @chiefCode
  
          
 SELECT RFI.Id,  
  RFI.FwkDomainOrganizationId,  
  FDO.InstitutionId,   
  FDO.Institution,           
        FDO.ChiefCode,  
        RFI .RoleID as 'RoleId',  
        RT.NAME as 'Role',   
       RFI.AdmSponsorTypeId as 'AdmSponsorTypeId',   
       AST.[Name] as AdmSponsorTypeName,           
       RFI.AdmPersonId, AP.LastName + ', ' + AP.FirstName + ' (' + FDU.UserName + ')' as FullName,          
  (Select top 1 ATN.Number from dbo.AdmTelephoneNumber ATN WHERE AdmTelephoneTypeId = 1 AND ATN.AdmPersonId = AP.Id) as Phone          
 FROM dbo.AdmResearchFinanceInfo RFI          
  INNER JOIN dbo.FwkDomainOrganization FDO ON FDO.Id = RFI.FwkDomainOrganizationId          
  INNER JOIN dbo.AdmSponsorType AST ON AST.Id = RFI.AdmSponsorTypeId          
  INNER JOIN dbo.AdmPerson AP ON AP.Id = RFI.AdmPersonId          
  INNER JOIN dbo.FwkDomainUser FDU ON AP.FwkDomainUserId = FDU.Id       
  INNER JOIN dbo.admRoleTypes RT ON RT.RoleId = RFI.RoleId  
 /* Search Filters*/
AND FDO.InstitutionId LIKE @institutionId +'%'
AND FDO.ChiefCode LIKE @strChiefCode +'%'
AND AP.LastName LIKE @lastName +'%'
AND AP.FirstName LIKE @firstName +'%'
         
            
 SET NOCOUNT OFF          
END 
GO
